package com.service;

import static com.def.GenericDef.E_READ_NOT_FOUND;
import static com.def.GenericDef.E_RET_WO;
import static com.def.GenericDef.PATH_TYPE_REWK;
import static com.def.GenericDef.PROC_WAIT;
import static com.def.GenericDef.RETURN_CODE_OK;
import static com.def.GenericDef.RETURN_CODE_SET_ERROR;
import static com.def.GenericDef.RETURN_CODE_UNKNOWN;
import static com.def.GenericDef.RETURN_MESG_OK;
import static com.def.GenericDef.TRX_OUT;
import static com.def.GenericDef._NORMAL;
import static com.def.GenericDef._NO;
import static com.def.GenericDef._YES;

import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.Map.Entry;

import com.dao.*;

import com.model.*;
import com.tx.Xpinqwor.*;
import org.apache.commons.lang.StringUtils;
import org.apache.log4j.Logger;
import org.hibernate.Query;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.context.annotation.Scope;
import org.springframework.stereotype.Service;
import org.springframework.transaction.interceptor.TransactionAspectSupport;

import com.icim.service.ICIMBaseService;
import com.mapKeyVal.key.StatBoxOpeBnkWoDpsKey;
import com.mapKeyVal.key.StatBoxOpeBnklotKey;
import com.rtnBean.NotFoundRtnBeen;
import com.rtnBean.SimpleRtnBeen;
import com.util.MqOpe;
import com.util.Util;

@Scope("prototype")
@Service("XpinqworService")
public class XpinqworService extends ICIMBaseService {

    private Logger logger = Logger.getLogger(XpinqworService.class);

    @Autowired
    private XpinqworI inTrx;

    @Autowired
    private XpinqworO outTrx;

    @Autowired
    private IRetBoxDao retBoxDao;

    @Autowired
    private IXplstworDao lstWorDao;

    @Autowired
    private IRetWODao retWODao;

    @Autowired
    private IBisMdlDefDao bisMdlDefDao;

    @Autowired
    private IRetPrdInfoDao retPrdInfoDao;

    @Autowired
    private IRetShipNoticeDao retShipNoticeDao;

    @Autowired
    private IRetPrdInDao retPrdInDao;

    @Autowired
    private IBisDataDao bisDataDao;

    private String gszMsgBuf = "";

    @Autowired
    private MqOpe mqOpe;

    @Autowired
    private IBisPathiDao bisPathiDao;

    public String subMainProc(String strInMsg) {
        String strOutTrx = null;
        inTrx = new XpinqworI();
        outTrx = new XpinqworO();
        outTrx.setTrx_id("XPINQWOR");
        outTrx.setType_id(TRX_OUT);
        outTrx.setRtn_code(String.valueOf(RETURN_CODE_SET_ERROR));
        try {
            inTrx = (XpinqworI) mqOpe.inStr2Obj(strInMsg, inTrx);
            char action_flgAry[] = inTrx.getAction_flg().toCharArray();
            char action_flg = action_flgAry[0];
            switch (action_flg) {
                case 'I':
                    outTrx = findWorderById(inTrx.getWorder_id());
                    break;
                case 'C':
                    outTrx = findLCD1(inTrx.getWorder_id());
                    break;
                case 'S':
                    sumWoInfo();
                    break;
                case 'L':
                    listWoBoxInfo();
                    break;
                case 'M':
                    summaryWoInfoFor9500();
                    break;
                case 'P':
                    getPrdListInfo();
                    break;
                default:
                    logger.info("mapping no action_flg ,error");
            }
            strOutTrx = mqOpe.obj2Str(outTrx);
        } catch (Exception ex) {
            TransactionAspectSupport.currentTransactionStatus()
                    .setRollbackOnly();
            outTrx.setRtn_mesg(ex.toString());
            outTrx.setRtn_code(RETURN_CODE_UNKNOWN);
            logger.error(Util.stackTraceToString(ex));
        } finally {
            logger.info(strOutTrx);
        }
        return strOutTrx;
    }


    private void getPrdListInfo() {
        List<Ret_prd_info> retPrdInfoList = null;
        List<XpinqworO5> oaryList = new ArrayList<XpinqworO5>();
        String hql = " FROM Ret_prd_info where wo_id_fk=:wo_id_fk";
        try {
            Query query = retPrdInfoDao.getCrSession().createQuery(hql);
            query.setString("wo_id_fk", inTrx.getWorder_id());
            retPrdInfoList = query.list();
        } catch (Exception ex) {
            logger.error("read RET_PRD_INFO ERROR:" + ex.getMessage());
            logger.error(Util.stackTraceToString(ex));
        }

        for (Ret_prd_info ret_prd_info : retPrdInfoList) {
            XpinqworO5 oary5 = new XpinqworO5();
            oary5.setPrd_seq_id(ret_prd_info.getPrd_seq_id());
            oary5.setFab_sn(ret_prd_info.getFab_sn());
            oary5.setSlot_no(ret_prd_info.getSlot_no());
            oary5.setPrd_stat(ret_prd_info.getPrd_stat());
            oary5.setBox_id_fk(ret_prd_info.getBox_id_fk());
            oary5.setPrd_grade(ret_prd_info.getPrd_grade());
            String pathId = PROC_WAIT.equals(ret_prd_info.getPrd_stat()) ? ret_prd_info.getNx_path_id_fk() : ret_prd_info.getCr_path_id_fk();
            String pathVer = PROC_WAIT.equals(ret_prd_info.getPrd_stat()) ? ret_prd_info.getNx_path_ver_fk() : ret_prd_info.getCr_path_ver_fk();
            Bis_pathiId id = new Bis_pathiId();
            id.setPath_id(pathId);
            id.setPath_ver(pathVer);
            Bis_pathi bis_pathi = bisPathiDao.get(id);
            if (bis_pathi != null) {
                if (bis_pathi.getPath_cate().equals(PATH_TYPE_REWK)) {
                    oary5.setOqc_skip_flg(_YES);
                } else {
                    oary5.setOqc_skip_flg(_NO);
                }
            }
            oary5.setBnk_flg(ret_prd_info.getBnk_flg());
            if (ret_prd_info.getPrd_stat().equals("INPR")) {
                oary5.setCr_ope_id(ret_prd_info.getCr_ope_id_fk());
                oary5.setCr_ope_no(ret_prd_info.getCr_ope_no_fk());
                oary5.setCr_ope_ver(ret_prd_info.getCr_ope_ver_fk());
                oary5.setCr_path_id(ret_prd_info.getCr_path_id_fk());
                oary5.setCr_path_ver(ret_prd_info.getCr_path_ver_fk());
                oary5.setCr_proc_id_fk(ret_prd_info.getCr_proc_id_fk());
            } else {
                oary5.setCr_ope_id(ret_prd_info.getNx_ope_id_fk());
                oary5.setCr_ope_no(ret_prd_info.getNx_ope_no_fk());
                oary5.setCr_ope_ver(ret_prd_info.getNx_ope_ver_fk());
                oary5.setCr_path_id(ret_prd_info.getNx_path_id_fk());
                oary5.setCr_path_ver(ret_prd_info.getNx_path_ver_fk());
                oary5.setCr_proc_id_fk(ret_prd_info.getNx_proc_id_fk());
            }
            oary5.setTool_id_fk(ret_prd_info.getTool_id_fk());
            oary5.setLot_id_fk(ret_prd_info.getLot_id());
            oary5.setMdl_id_fk(ret_prd_info.getMdl_id_fk());
            oary5.setSo_id_fk(ret_prd_info.getSo_id_fk());
            oary5.setWo_id_fk(ret_prd_info.getWo_id_fk());
            oary5.setMtrl_box_id(ret_prd_info.getMtrl_box_id_fk());
            oaryList.add(oary5);
        }
        outTrx.setRtn_code(RETURN_CODE_OK);
        outTrx.setRtn_mesg(RETURN_MESG_OK);
        outTrx.setOary5(oaryList);
        outTrx.setOary5_cnt(oaryList.size());
    }

    private void listWoBoxInfo() {
        String hql = "FROM Ret_box where wo_id_fk =:wo_id_fk ";
        Query query = retBoxDao.getCrSession().createQuery(hql);
        query.setString("wo_id_fk", inTrx.getWorder_id());
        List<Ret_box> retBoxList = query.list();
        List<XpinqworOB> oaryList = new ArrayList<XpinqworOB>();
        for (Ret_box ret_box : retBoxList) {
            XpinqworOB oary = new XpinqworOB();
            oary.setBox_id(ret_box.getBox_id());
            oary.setLot_id(ret_box.getLot_id_fk());
            oary.setOpe_id(ret_box.getNx_ope_id_fk());
            oary.setOpe_ver(ret_box.getNx_ope_ver_fk());
            oaryList.add(oary);
        }
        outTrx.setOary(oaryList);
        outTrx.setTbl_cnt(oaryList.size());
        outTrx.setRtn_code(RETURN_CODE_OK);
        outTrx.setRtn_mesg(RETURN_MESG_OK);
    }

    /**
     * Query many wo's info summary
     */
    public void sumWoInfo() {
        SimpleRtnBeen rtnBeen = new SimpleRtnBeen(_NORMAL);

        rtnBeen = sumWoInfoReal(inTrx.getWorder_id());
        if (rtnBeen.getlRc() != _NORMAL) {
            outTrx.setRtn_code(String.valueOf(rtnBeen.getlRc()));
            outTrx.setRtn_mesg(rtnBeen.getMsgBuf());
        } else {
            outTrx.setRtn_code(String.valueOf(RETURN_CODE_OK));
            outTrx.setRtn_mesg(RETURN_MESG_OK);
        }
    }

    private SimpleRtnBeen sumWoInfoReal(String wo_id) {
        Ret_wo ret_so = new Ret_wo();
        XpinqworOA out1 = new XpinqworOA();
        ;
        String so_id = "";

        // Get wo
        Ret_wo ret_wo = new Ret_wo();
        ret_wo = retWODao.get(wo_id);
        if (null == ret_wo) {
            gszMsgBuf = new NotFoundRtnBeen(wo_id + "[W]", Ret_wo.class).toString();
            return new SimpleRtnBeen(E_RET_WO + E_READ_NOT_FOUND, gszMsgBuf);
        }
        out1.setWo_tb(ret_wo);

        // Get so
        so_id = ret_wo.getSo_id();
        if (!StringUtils.isEmpty(so_id)) {
            ret_so = retWODao.get(so_id);
            if (ret_so != null) {
                out1.setSo_tb(ret_so);
            }
        }
        outTrx.setOut1(out1);

        return new SimpleRtnBeen(_NORMAL);
    }

    public XpinqworO findWorderById(String wo_id) {
        Ret_wo retWo = new Ret_wo();
        XpinqworO xpinqworO = new XpinqworO();
        try {

        } catch (Exception ex) {
            logger.error(Util.stackTraceToString(ex));
            xpinqworO.setTrx_id("XPINQWOR");
            xpinqworO.setType_id("O");
            xpinqworO.setRtn_code("0000001");
            xpinqworO.setRtn_mesg(ex.getMessage());
        }
        String querySql = " from Ret_wo where wo_id= '" + wo_id + "'";
        List<Ret_wo> retWoList = lstWorDao.find(querySql);
        xpinqworO.setTrx_id("XPINQWOR");
        xpinqworO.setType_id("O");
        xpinqworO.setRtn_code("0000000");
        xpinqworO.setRtn_mesg("success");
        if (retWoList.size() > 0) {
            retWo = retWoList.get(0);
            xpinqworO.setWo_id(retWo.getWo_id());
            xpinqworO.setWo_cate(retWo.getWo_cate());
            xpinqworO.setWo_dsc(retWo.getWo_dsc());
            xpinqworO.setWo_typ(retWo.getWo_typ());
            xpinqworO.setWo_prty(retWo.getWo_prty());
            xpinqworO.setWo_stat(retWo.getWo_stat());
            xpinqworO.setSo_id(retWo.getSo_id());
            xpinqworO.setMdl_id_fk(retWo.getMdl_id_fk());
            xpinqworO.setPln_stb_date(retWo.getPln_stb_date().toString());
            xpinqworO.setPln_cmp_date(retWo.getPln_cmp_date().toString());
            xpinqworO.setErp_path_id(retWo.getErp_path_id());
            xpinqworO.setErp_path_ver(retWo.getErp_path_ver());
            xpinqworO.setErp_bom_id(retWo.getErp_bom_id());
            xpinqworO.setPath_id_fk(retWo.getPath_id_fk());
            xpinqworO.setPath_ver_fk(retWo.getPath_ver_fk());
            xpinqworO.setStr_ope_no(retWo.getStr_ope_no());
            xpinqworO.setPln_prd_qty(retWo.getPln_prd_qty());
            xpinqworO.setRl_prd_qty(retWo.getRl_prd_qty());
            xpinqworO.setComp_prd_qty(retWo.getComp_prd_qty());
            xpinqworO.setWh_in_prd_qty(retWo.getWh_in_prd_qty());
            xpinqworO.setScrp_prd_qty(retWo.getScrp_prd_qty());
            xpinqworO.setDest_shop(retWo.getDest_shop());
            xpinqworO.setPlant_id(retWo.getPlant_id());
            xpinqworO.setSwh_cnt_flg(retWo.getSwh_cnt_flg());
            xpinqworO.setOpe_eff_flg(retWo.getOpe_eff_flg());
            xpinqworO.setVcr_flg_fk(retWo.getVcr_flg_fk());
            xpinqworO.setEvt_user(retWo.getEvt_usr());
            xpinqworO.setEvt_timestamp(retWo.getEvt_timestamp().toString());
            xpinqworO.setMdl_cate_fk(retWo.getMdl_cate_fk());
        }
        return xpinqworO;
    }

    private XpinqworO findLCD1(String lot_id) {
        Ret_wo retWo = new Ret_wo();
        XpinqworO xpinqworO = new XpinqworO();
        try {

        } catch (Exception ex) {
            logger.error(Util.stackTraceToString(ex));
            xpinqworO.setTrx_id("XPINQWOR");
            xpinqworO.setType_id("O");
            xpinqworO.setRtn_code("0000001");
            xpinqworO.setRtn_mesg(ex.getMessage());
        }

        String wo_id = lot_id.split("-")[0];

        String querySql = " From Ret_wo where 1 = 1";
        querySql += " and wo_id= '" + wo_id + "'";
        List<Ret_wo> retWoList = lstWorDao.find(querySql);

        if (retWoList.size() > 0) {
            String hql2 = "From Ret_prd_info where 1 = 1";
//			hql2 += " and box_id_fk = '" + ret_box.getBox_id() + "'";
            hql2 += " and lot_id = '" + lot_id + "'";
            List<Ret_prd_info> retPrdInfoList = retPrdInDao.find(hql2);

            if(retPrdInfoList != null && retPrdInfoList.size() >0){
               String mtrl_id=retPrdInfoList.get(0).getMdl_id_fk();
                Bis_mdl_def bismdldef = bisMdlDefDao.get(mtrl_id);
                String mdlSize =bismdldef.getMdl_size();
                String boxIdFk= retPrdInfoList.get(0).getBox_id_fk();
                retWo = retWoList.get(0);
                String mdlCateFk = retWo.getMdl_cate_fk();
                String woCate = retWo.getWo_cate();
                String soId = retWo.getSo_id();
                String hql3 = "From Bis_data where data_cate ='MDCT'";
                hql3 += " and data_ext = '" + mdlCateFk + "'";
                List<Bis_data> bisDataList = bisDataDao.find(hql3);
                String hql4 = "From Bis_data where data_cate ='WOAB'";
                hql4 += " and data_id = '" + woCate + "'";
                List<Bis_data> bisDataList2 = bisDataDao.find(hql4);
                String mdlCateDsc = bisDataList.get(0).getData_desc();
                String woCateDsc = bisDataList2.get(0).getData_desc();
                List<XpinqworO6> oaryList = new ArrayList<>();
                for (int j = 0; j < retPrdInfoList.size(); j++) {
                    XpinqworO6 xpinqworO6 = new XpinqworO6();
//					xpinqworO6.setLot_id(ret_box.getLot_id_fk());
                    xpinqworO6.setLot_id(lot_id);
                    xpinqworO6.setWo_id(wo_id);
                    xpinqworO6.setSoId(soId);
                    if (retWo.getPln_prd_qty() != null) {
                        xpinqworO6.setPlan_qty(retWo.getPln_prd_qty().toString());
                    }
                    xpinqworO6.setMtrl_cate(mdlCateFk);
                    xpinqworO6.setSlot_no(retPrdInfoList.get(j).getSlot_no());
                    xpinqworO6.setMdl_cate(mdlCateDsc);
                    xpinqworO6.setWo_cate_dsc(woCateDsc);
                  Ret_wo  retWo1=retWODao.get(retWo.getSo_id());
                  if(retWo1!=null){
                      if ("Y".equals(retWo1.getOpe_eff_flg())){
                          xpinqworO6.setOldBox("原箱原位回");
                      }else if("Y".equals(retWo1.getVcr_flg_fk())){
                          xpinqworO6.setOldBox("原箱回");
                      }else{
                          xpinqworO6.setOldBox("无要求");
                      }
                  }
                    xpinqworO6.setCus_id(retWo.getCus_id_fk());
                    xpinqworO6.setFm_mdl_id_fk(retWo.getMtrl_prod_id_fk());
                    xpinqworO6.setTh_mdl_id_fk(retWo.getTh_mdl_id_fk());
                    xpinqworO6.setPrd_qty(String.valueOf(retPrdInfoList.size()));
                    if(!StringUtils.isEmpty(boxIdFk)){
                        xpinqworO6.setBox_id(boxIdFk);
                    }
                    String mtrlBoxIdFk = retPrdInfoList.get(0).getMtrl_box_id_fk();
                    if (!StringUtils.isEmpty(mtrlBoxIdFk)) {
                        xpinqworO6.setMtrl_box_id(mtrlBoxIdFk);
                    }
                    if("006".equals(retWo.getCus_id_fk())||"007".equals((retWo.getCus_id_fk()))){
                        if (retWo.getMdl_id_fk() != null) {
                            Bis_mdl_def bis_mdl_def = bisMdlDefDao.get(retWo.getMdl_id_fk());
                            if (bis_mdl_def != null) {
                                xpinqworO6.setLayout_id_fk(bis_mdl_def.getLayot_id_fk());
                                xpinqworO6.setMdl_Guige(bis_mdl_def.getMdl_typ());
                            }
                        }
                    } else {
                        xpinqworO6.setLayout_id_fk(retWo.getLayoutId());
                        xpinqworO6.setMdl_Guige(retWo.getSpec());
                    }

                    xpinqworO6.setMtrl_size(mdlSize);
                    xpinqworO6.setCrt_timestamp(retWo.getCrt_timestamp().toString());
                    xpinqworO6.setPrd_id(retPrdInfoList.get(j).getPrd_seq_id());
                    oaryList.add(xpinqworO6);
                }
                xpinqworO.setOary6(oaryList);
            }
        }
        xpinqworO.setTrx_id("XPINQWOR");
        xpinqworO.setType_id("O");
        xpinqworO.setRtn_code("0000000");
        xpinqworO.setRtn_mesg("success");
        return xpinqworO;
    }


    private void summaryWoInfoFor9500() {
        String woId = inTrx.getWorder_id();
        String opeId, prdGrade, boxId, prdStat, lotId;
        Map<StatBoxOpeBnklotKey, Integer> wipMap = new HashMap<StatBoxOpeBnklotKey, Integer>();//在制品
        Map<String, Integer> boxMap = new HashMap<String, Integer>();//箱内数量
        Map<String, Integer> gradeMap = new HashMap<String, Integer>();//LZ,GK,NG品数量
        List<XpinqworO3> oary3List = new ArrayList<XpinqworO3>();

        Ret_wo retWo = retWODao.get(woId);
        if (retWo == null) {
            outTrx.setRtn_code(woId);
            outTrx.setRtn_mesg("内部订单找不到记录:" + woId);
            return;
        }
        String hql = "FROM Ret_prd_info where wo_id_fk='" + woId + "'";

        List<Ret_prd_info> retPrdInfoList = new ArrayList<Ret_prd_info>();
        retPrdInfoList = retPrdInfoDao.find(hql);
        for (Ret_prd_info retPrdInfo : retPrdInfoList) {
            lotId = retPrdInfo.getLot_id();
            prdStat = retPrdInfo.getPrd_stat();
            boxId = retPrdInfo.getBox_id_fk();
            opeId = "INPR".equals(prdStat) ? retPrdInfo.getCr_ope_id_fk() : retPrdInfo
                    .getNx_ope_id_fk();
            //统计在制数量
            StatBoxOpeBnklotKey wipKey = new StatBoxOpeBnklotKey(retPrdInfo.getPrd_stat(), opeId, boxId, retPrdInfo.getBnk_flg(), lotId, retPrdInfo.getMtrl_box_id_fk());
            if (wipMap.containsKey(wipKey)) {
                wipMap.put(wipKey, wipMap.get(wipKey) + 1);
            } else {
                wipMap.put(wipKey, 1);
            }
            //统计箱内数量
            if (boxId != null) {
                if (boxMap.containsKey(boxId)) {
                    boxMap.put(boxId, boxMap.get(boxId) + 1);
                } else {
                    boxMap.put(boxId, 1);
                }
            }
            //统计LZ,GK,NG品数量
            prdGrade = retPrdInfo.getPrd_grade();
            if (prdGrade != null) {
                if (gradeMap.containsKey(prdGrade)) {
                    gradeMap.put(prdGrade, gradeMap.get(prdGrade) + 1);
                } else {
                    gradeMap.put(prdGrade, 1);
                }
            }

        }
        //返回内部订单信息
        Iterator<Entry<StatBoxOpeBnklotKey, Integer>> iter = wipMap.entrySet().iterator();
        List<XpinqworO1> oary1List = new ArrayList<XpinqworO1>();

        StatBoxOpeBnklotKey wipKey = null;
        while (iter.hasNext()) {
            Entry<StatBoxOpeBnklotKey, Integer> entry = iter.next();
            wipKey = entry.getKey();
            prdStat = wipKey.getPrd_stat();
            boxId = wipKey.getBox_id();
            lotId = wipKey.getLot_id();
            XpinqworO1 oary1 = new XpinqworO1();
            String hqldest = "";
            if (!("").equals(boxId)) {
                Ret_box retBox = retBoxDao.get(boxId);
                if (null != retBox) {
                    if (!Util.isSpaceCheck(retBox.getDest_shop())) {
                        if (!Util.isSpaceCheck(retBox.getBox_def())) {
                            hqldest = "FROM Bis_data WHERE data_cate = 'DEST' AND data_id = '" + retBox.getBox_def() + "'";
                        } else {
                            hqldest = "FROM Bis_data WHERE data_cate = 'DEST' AND data_id = '" + retBox.getDest_shop() + "'";
                        }
                        List<Bis_data> dataList = bisDataDao.find(hqldest);
                        if (!dataList.isEmpty()) {
                            oary1.setDes_data_desc(dataList.get(0).getData_desc());
                        }
                    }
                }
            }
            oary1.setCus_id(retWo.getCus_id_fk());
            oary1.setSo_id(retWo.getSo_id());
            oary1.setWo_stat(retWo.getWo_stat());
            oary1.setLot_id(wipKey.getLot_id());
            oary1.setBox_id(boxId);
            oary1.setMtrl_box_id_fk(wipKey.getMtrl_box_id());
            oary1.setPrd_qty(fmtInt(boxMap.get(boxId)));
            oary1.setWip_qty(fmtInt(entry.getValue()));
            oary1.setOpe_id(wipKey.getOpe_id());
            oary1.setPrd_stat(wipKey.getPrd_stat());
            oary1.setBnk_flg(wipKey.getBnk_flg());
            oary1.setMdl_id(retWo.getMdl_id_fk());
            oary1.setFm_mdl_id(retWo.getFm_mdl_id_fk());
            oary1.setCut_mdl_id(retWo.getCut_mdl_id_fk());
            oary1.setMtrl_prod_id(retWo.getMtrl_prod_id_fk());
            oary1List.add(oary1);
        }
        //返回未发料和已发料的箱子信息
        StringBuffer retPrdInSql = new StringBuffer("FROM Ret_prd_in where wo_id_fk = ?");
        retPrdInSql.append(" and ppbox_stat in ('WFRL','WAIT') order by mtrl_box_id");
        List<Ret_prd_in> retPrdInList = retPrdInDao.list(retPrdInSql.toString(), woId);

        if (!retPrdInList.isEmpty()) {
            StringBuffer sql_cnt = new StringBuffer("");
            for (Ret_prd_in ret_prd_in : retPrdInList) {
                sql_cnt.append("'" + ret_prd_in.getId().getMtrl_box_id() + "',");
            }
            sql_cnt.deleteCharAt(sql_cnt.length() - 1);

            String sumSqlStr = "SELECT B.MTRL_BOX_ID_FK,B.PRD_COMP_QTY,A.MTRL_BOX_ID,A.CUS_ID,A.MTRL_PROD_ID_FK,A.MTRL_LOT_ID,A.PRD_ACT_QTY,A.PPBOX_STAT,A.PRD_PROC_QTY,A.STB_SHOP FROM " +
                    "(" +
                    "SELECT * FROM RET_PRD_IN WHERE  wo_id_fk = '" + woId + "' AND ppbox_stat IN ('WFRL','WAIT') " +
                    ")AS A " +
                    "LEFT JOIN " +
                    "(" +
                    "SELECT MTRL_BOX_ID_FK,SUM(CASE WHEN PRD_STAT IN ('COMP','SHIP','SHTC') THEN 1 ELSE 0 END) PRD_COMP_QTY " +
                    "FROM RET_PRD_INFO WHERE MTRL_BOX_ID_FK " +
                    "IN (" + sql_cnt + ") " +
                    "GROUP BY MTRL_BOX_ID_FK " +
                    ")AS B " +
                    "ON A.MTRL_BOX_ID = B.MTRL_BOX_ID_FK";

            List<Object[]> prdInfObjects = retPrdInfoDao.findBySQL(sumSqlStr);

            for (int i = 0; i < prdInfObjects.size(); i++) {
                Object prdIn[] = prdInfObjects.get(i);
                String box_id = "";
                String cus_id = "";
                String mtrl_prod_id = "";
                String lot_id = "";
                String prd_qty = "";
                String ppbox_stat = "";
                String prd_proc_qty = "";
                String stb_shop = "";
                if (prdIn[2] != null) {
                    box_id = prdIn[2].toString();
                }
                if (prdIn[3] != null) {
                    cus_id = prdIn[3].toString();
                }
                if (prdIn[4] != null) {
                    mtrl_prod_id = prdIn[4].toString();
                }
                if (prdIn[5] != null) {
                    lot_id = prdIn[5].toString();
                }
                if (prdIn[6] != null) {
                    prd_qty = prdIn[6].toString();
                }
                if (prdIn[7] != null) {
                    ppbox_stat = prdIn[7].toString();
                }
                if (prdIn[8] != null) {
                    prd_proc_qty = prdIn[8].toString();
                }
                if (prdIn[9] != null) {
                    stb_shop = prdIn[9].toString();
                }
                String so_id = retWo.getSo_id();
                String mdl_id = retWo.getMdl_id_fk();
                String fm_mdl_id = retWo.getFm_mdl_id_fk();
                String cut_mdl_id = retWo.getCut_mdl_id_fk();
                String wo_stat = retWo.getWo_stat();

                XpinqworO1 oary1 = new XpinqworO1();
                oary1.setBox_id(box_id);
                oary1.setCus_id(cus_id);
                oary1.setSo_id(so_id);
                oary1.setMtrl_prod_id(mtrl_prod_id);
                oary1.setMdl_id(mdl_id);
                oary1.setFm_mdl_id(fm_mdl_id);
                oary1.setCut_mdl_id(cut_mdl_id);
                oary1.setWo_stat(wo_stat);
                oary1.setLot_id(lot_id);
                oary1.setPrd_qty(Integer.valueOf(prd_qty));

                if (prdIn[0] == null || prdIn[1] == null) {
                    oary1.setWip_qty(Integer.valueOf(prd_proc_qty));
                } else {
                    String comcnt = prdIn[1].toString();
                    oary1.setWip_qty(Integer.valueOf(prd_proc_qty) - Integer.valueOf(comcnt));
                }

                if ("WAIT".equals(ppbox_stat)) {
                    oary1.setPrd_stat("未发料");
                } else if ("WFRL".equals(ppbox_stat)) {
                    oary1.setPrd_stat("已发料");
                } else if ("RELS".equals(ppbox_stat)) {
                    oary1.setPrd_stat("已提配");
                }
                if (!Util.isSpaceCheck(stb_shop)) {
                    String hqldest = "FROM Bis_data WHERE data_cate = 'DEST' AND data_id = '" + stb_shop + "'";
                    List<Bis_data> dataList = bisDataDao.find(hqldest);
                    if (null != dataList) {
                        oary1.setDes_data_desc(dataList.get(0).getData_desc());
                    }
                }
                oary1.setOpe_id("来料检");
                oary1List.add(oary1);
            }
        }

        //返回内部订单数量信息
        List<XpinqworO2> oary2List = new ArrayList<XpinqworO2>();
        XpinqworO2 oary2 = new XpinqworO2();
        oary2.setPln_prd_qty(fmtInt(retWo.getPln_prd_qty()));
        oary2.setRl_prd_qty(fmtInt(retWo.getRl_prd_qty()));
        oary2.setUn_rl_prd_qty(fmtInt(retWo.getPln_prd_qty()) - fmtInt(retWo.getRl_prd_qty()));
        oary2.setComp_prd_qty(fmtInt(retWo.getComp_prd_qty()) + fmtInt(retWo.getWh_in_prd_qty()) + fmtInt(retWo.getWh_out_prd_qty() + fmtInt(retWo.getWip_wh_in_prd_qty())));
        oary2.setUn_comp_prd_qty(fmtInt(retWo.getPln_prd_qty()) - fmtInt(oary2.getComp_prd_qty()));
        oary2.setWh_out_prd_qty(fmtInt(retWo.getWh_out_prd_qty()));
        oary2.setUn_wh_out_prd_qty(fmtInt(retWo.getPln_prd_qty()) - fmtInt(retWo.getWh_out_prd_qty()));
        oary2.setGrade_gk_qty(fmtInt(gradeMap.get("GK")));
        oary2.setGrade_lz_qty(fmtInt(gradeMap.get("LZ")));
        oary2.setGrade_ng_qty(fmtInt(gradeMap.get("NG")));
        oary2.setGrade_ok_qty(fmtInt(gradeMap.get("OK")));
        oary2.setGrade_sc_qty(fmtInt(gradeMap.get("SC")));
        oary2List.add(oary2);
        //返回交货订单信息
        hql = "FROM Ret_ship_notice where wo_id_fk='" + woId + "'";
        List<Ret_ship_notice> retShipNoticeList = retShipNoticeDao.find(hql);
        for (Ret_ship_notice retShipNotice : retShipNoticeList) {
            XpinqworO3 oary3 = new XpinqworO3();
            oary3.setCus_id(retWo.getCus_id_fk());
            oary3.setDn_no(retShipNotice.getDn_no());
            oary3.setMdl_id(retWo.getMdl_id_fk());
            oary3.setShip_qty(fmtInt(retShipNotice.getShip_qty()));
            oary3.setShip_date(retShipNotice.getShip_date());
            oary3.setPlan_shipping_timestamp(retShipNotice.getShip_date().toString() + " " + retShipNotice.getShip_time().toString());
            oary3.setShipping_timestamp(retShipNotice.getShipping_timestamp());
            oary3.setWh_out_prd_qty(fmtInt(retShipNotice.getWh_out_prd_qty()));
            oary3List.add(oary3);
        }
        List<XpinqworO7> oary7List = new ArrayList<XpinqworO7>();
        String hql1 = "FROM Ret_prd_info where wo_id_fk='" + woId + "'";
        List<Ret_prd_info> retPrdInfos1 = retPrdInfoDao.find(hql1);
        if (retPrdInfos1.size() > 0) {
            for (Ret_prd_info ret_prd_info : retPrdInfos1) {
                XpinqworO7 oary7 = new XpinqworO7();
                oary7.setPrd_seq_id(ret_prd_info.getPrd_seq_id());
                oary7.setBox_id(ret_prd_info.getBox_id_fk());
                oary7.setPrd_grade(ret_prd_info.getPrd_grade());
                oary7.setMtrl_box_id(ret_prd_info.getMtrl_box_id_fk());
                oary7List.add(oary7);
            }
        }

        outTrx.setOary1(oary1List);
        outTrx.setOary1_cnt(oary1List.size());
        outTrx.setOary2(oary2List);
        outTrx.setOary2_cnt(oary2List.size());
        outTrx.setOary3(oary3List);
        outTrx.setOary3_cnt(oary3List.size());
        outTrx.setOary7(oary7List);
        outTrx.setRtn_code(RETURN_CODE_OK);
        outTrx.setRtn_mesg(RETURN_MESG_OK);
    }

    private Integer fmtInt(Integer val) {
        return val == null ? 0 : val;
    }

}
